package com.wutianyi.datastore;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
import com.wutianyi.datastore.DataStoreProtos.Properties;

/**
 * @author hanjiewu
 * 
 */
public class DataStoreServicesMySqlImpl extends AbstractDataStoreServicesImpl
{
    private static Logger logger = LoggerFactory.getLogger(DataStoreServicesMySqlImpl.class);

    /**
     * 数据库连接池
     */
    private BoneCP connectionPool = null;
    /**
     * 索引的全部表名称
     */
    private Set<String> indexTables = new HashSet<String>();

    /**
     * 索引的类型
     */
    private Map<String, String> indexTypes = new HashMap<String, String>();

    /**
     * 现有的保存数据的表
     */
    private Set<String> kindTables = new HashSet<String>();

    /**
     * 表有的索引
     */
    private Map<String, Set<String>> kindIndexColumns = new HashMap<String, Set<String>>();

    private final String joinChars = "___";

    /**
     * 索引的前缀
     */
    private final String INDEX_PREFIX = "__kind__indexes__";
    /**
     * kind的前缀
     */
    private final String KIND_PREFIX = "__kind_table__";

    private final String KIND_VALUE_SUFFIX = "_value";
    /**
     * 索引表的模板
     */
    private MessageFormat messageFormat = new MessageFormat("create table {0}(i {1},value int,unique key(i, value))");
    /**
     * 插入索引的sql 模板
     */
    private MessageFormat indexSqlFormat = new MessageFormat("insert ignore into {0}(i,value) values({2},{3});");

    private MessageFormat deleteIndexDataFormat = new MessageFormat("delete from {0} where i={1} and value={2};");

    private String db = null;

    private final String DEFAULT_CHARSET = "ISO-8859-1";

    /**
     * @param db
     *            初始化
     */
    public DataStoreServicesMySqlImpl(String db)
    {
        try
        {
            this.db = db;
            BoneCPConfig config = new BoneCPConfig();
            config.setJdbcUrl("jdbc:mysql://localhost:3307/" + db + "?characterEncoding=utf8");
            config.setUsername("root");
            config.setPassword("860728");
            config.setMinConnectionsPerPartition(5);
            config.setMaxConnectionsPerPartition(10);
            config.setPartitionCount(1);
            connectionPool = new BoneCP(config);

            Connection conn = connectionPool.getConnection();
            ResultSet rs = conn.createStatement().executeQuery("show tables like '" + INDEX_PREFIX + "%'");
            while (rs.next())
            {
                String t = rs.getString(1);
                indexTables.add(t.toLowerCase());
                indexTypes.put(t, getIndexTypes(t));
                String ts = t.substring(INDEX_PREFIX.length());
                String[] tts = ts.split(joinChars);
                if (tts.length == 2)
                {
                    Set<String> columns = kindIndexColumns.get(tts[0]);
                    if (null == columns)
                    {
                        columns = new HashSet<String>();
                        kindIndexColumns.put(tts[0], columns);
                    }
                    columns.add(tts[1]);
                }
            }
            rs = conn.createStatement().executeQuery("show tables like '" + KIND_PREFIX + "%'");
            while (rs.next())
            {
                String t = rs.getString(1);
                kindTables.add(t);
            }
            conn.close();
        }
        catch (Exception e)
        {
            logger.error("", e);
        }
    }

    @Override
    protected void storeEntity(Entity entity, Properties properties)
    {

        initIndexTable(entity);

        initKingTable(entity);
        Connection conn = null;
        try
        {
            String table = KIND_PREFIX + entity.getKind();
            String tableValue = table + KIND_VALUE_SUFFIX;

            conn = connectionPool.getConnection();
            conn.setAutoCommit(false);

            logger.debug("Inserting Index Datas ...");

            String value = new String(properties.toByteArray(), DEFAULT_CHARSET);

            String sql = "insert ignore into " + table + "(k)values('" + entity.getKey() + "')";

            logger.debug("Insert Kind Sql: {}", sql);

            int r = conn.createStatement().executeUpdate(sql);

            logger.debug("The Key: {} already exist {}", r == 0 ? true : false);

            sql = "select id from " + table + " where k='" + entity.getKey() + "'";

            logger.debug("Select Key Kind Id: " + sql);

            ResultSet rs = conn.createStatement().executeQuery(sql);
            int id = 0;
            if (rs.next())
            {
                id = rs.getInt(1);
            }
            if (id == 0)
            {
                return;
            }
            StringBuilder builder = new StringBuilder();
            deleteOldIndexDatas(entity, builder, id, r == 0 ? false : true);

            if (builder.length() > 0)
            {
                logger.debug("delete old index datas: {}", builder.toString());

                conn.createStatement().execute(builder.toString());
                builder.delete(0, builder.length());
            }

            initIndexDatas(entity, builder, id);
            if (builder.length() > 0)
            {
                conn.createStatement().execute(builder.toString());
            }

            logger.debug("Entity Id: {}", id);
            sql = "delete from " + tableValue + " where id = " + id;

            logger.debug("Delete Entity Old Value Sql: " + sql);

            conn.createStatement().execute(sql);
            int len = value.length();
            if (len > 0)
            {
                int start = 0;
                int end = 3999;
                int order = 1;
                sql = "insert into " + tableValue + "(id,value,o)values(?, ?, ?)";
                PreparedStatement psmt = null;
                while (end < len)
                {
                    psmt = conn.prepareStatement(sql);
                    psmt.setInt(1, id);
                    psmt.setString(2, value.substring(start, end));
                    psmt.setInt(3, order);
                    psmt.execute();

                    start += 3999;
                    end += 3999;
                    ++order;
                }

                psmt = conn.prepareStatement(sql);
                logger.debug("Insert Kind Value Sql: {}", sql);

                psmt.setInt(1, id);
                psmt.setString(2, value.substring(start, len));
                psmt.setInt(3, order);
                psmt.execute();
                entity.setId(id);
            }
        }
        catch (SQLException e)
        {
            try
            {
                conn.rollback();
            }
            catch (SQLException e1)
            {
                logger.error("", e1);
            }
            logger.error("", e);
        }
        catch (UnsupportedEncodingException e)
        {
            logger.error("", e);
        }
        finally
        {
            try
            {
                conn.commit();
                conn.close();
            }
            catch (SQLException e)
            {
                logger.error("", e);
            }
        }

    }

    @SuppressWarnings("rawtypes")
    private void deleteOldIndexDatas(Entity entity, StringBuilder builder, int id, boolean b)
    {
        Map<String, Object> originalIndexProperties = entity.getOriginalIndexProperties();
        if (null != originalIndexProperties)
        {
            for (Entry<String, Object> entry : originalIndexProperties.entrySet())
            {
                Object v = entry.getValue();
                String k = entry.getKey();
                if (null != v && !v.equals(entity.getProperty(k)))
                {
                    Object[] ps = new Object[3];
                    ps[0] = getIndexTable(entity.getKind(), k);
                    ps[1] = v;
                    ps[2] = id;
                    Class clz = v.getClass();
                    if (clz.equals(String.class))
                    {
                        ps[1] = "'" + StringEscapeUtils.escapeSql(v.toString()) + "'";
                    }
                    builder.append(deleteIndexDataFormat.format(ps));
                }
            }
        }
        else
        {
            if (!b)
            {
                Set<String> indexes = kindIndexColumns.get(entity.getKind());
                if (null != indexes)
                {
                    for (String index : indexes)
                    {
                        String table = getIndexTable(entity.getKind(), index);
                        String sql = "delete from " + table + " where value = " + id;
                        builder.append(sql);
                        builder.append(';');
                    }
                }
            }
        }
    }

    /**
     * @param entity
     * @param builder
     * @param id
     *            创建索引数据的插入sql
     */
    private void initIndexDatas(Entity entity, StringBuilder builder, int id)
    {
        if (null != entity.getPropertiesInIndexes())
        {
            for (Entry<String, Boolean> entry : entity.getPropertiesInIndexes().entrySet())
            {
                if (entry.getValue())
                {
                    String it = getIndexTable(entity.getKind(), entry.getKey());
                    {
                        Object v = entity.getProperty(entry.getKey());

                        Object[] values = getIndexParameterValues(it, v, entry.getKey());
                        values[3] = id;
                        String insertSql = indexSqlFormat.format(values);
                        builder.append(insertSql);
                    }
                }
            }
        }

    }

    private String getIndexTable(String kind, String key)
    {
        return (INDEX_PREFIX + kind + joinChars + key).toLowerCase();
    }

    /**
     * @param entity
     *            创建表，用于保存值信息
     */
    private void initKingTable(Entity entity)
    {
        String table = (KIND_PREFIX + entity.getKind()).toLowerCase();
        if (!kindTables.contains(table))
        {
            synchronized (kindTables)
            {
                if (!kindTables.contains(table))
                {
                    try
                    {
                        Connection conn = connectionPool.getConnection();
                        String sql = "create table " + table
                                + " (id int primary key auto_increment,k varchar(128) unique key)";
                        logger.debug("Creating Kind Table: {}...", table);
                        logger.debug("Create Kind Table Sql: {}", sql);

                        conn.createStatement().execute(sql);
                        sql = "create table " + table + KIND_VALUE_SUFFIX
                                + "(id int,value varchar(3999), o int,index(id))";

                        logger.debug("Creating Kind Value Table: {}...", table + KIND_VALUE_SUFFIX);
                        logger.debug("Create Kind Value Table Sql: {}", sql);
                        conn.createStatement().execute(sql);
                        kindTables.add(table);
                        conn.close();
                    }
                    catch (SQLException e)
                    {
                        logger.error("", e);
                    }
                }
            }
        }
    }

    /**
     * @param entity
     *            先对索引进行初始化
     */
    private void initIndexTable(Entity entity)
    {
        if (null != entity.getPropertiesInIndexes())
        {
            for (Entry<String, Boolean> entry : entity.getPropertiesInIndexes().entrySet())
            {
                if (entry.getValue())
                {
                    String it = getIndexTable(entity.getKind(), entry.getKey());
                    if (!indexTables.contains(it))
                    {
                        synchronized (indexTables)
                        {
                            if (!indexTables.contains(it))
                            {
                                Object v = entity.getProperty(entry.getKey());

                                Object[] values = getIndexParameterValues(it, v, entry.getKey());

                                String sql = messageFormat.format(values);

                                logger.debug("Create Index Table Sql: {}", sql);

                                try
                                {
                                    Connection conn = connectionPool.getConnection();
                                    conn.createStatement().execute(sql);
                                    conn.close();
                                    indexTables.add(it);
                                    indexTypes.put(it, getIndexTypes(it));
                                    Set<String> columns = kindIndexColumns.get(entity.getKind());
                                    if (null == columns)
                                    {
                                        columns = new HashSet<String>();
                                        kindIndexColumns.put(entity.getKind(), columns);
                                    }
                                    columns.add(entry.getKey());
                                }
                                catch (SQLException e)
                                {
                                    logger.error("", e);
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * @param tableName
     * @param v
     * @param key
     * @return 获取索引列的类型的值，用户创建sql或插入sql的初始化
     */
    @SuppressWarnings("rawtypes")
    private Object[] getIndexParameterValues(String tableName, Object v, String key)
    {
        Object[] values = new Object[4];
        values[0] = tableName;
        Class clz = v.getClass();
        values[2] = v;
        if (clz.equals(int.class) || clz.equals(Integer.class))
        {
            values[1] = "int";
        }
        else if (clz.equals(long.class) || clz.equals(Long.class))
        {
            values[1] = "long";
        }
        else if (clz.equals(double.class) || clz.equals(Double.class))
        {
            values[1] = "double";
        }
        else if (clz.equals(float.class) || clz.equals(Float.class))
        {
            values[1] = "float";
        }
        else if (clz.equals(boolean.class) || clz.equals(Boolean.class))
        {
            values[1] = "boolean";
        }
        else if (clz.equals(String.class))
        {
            values[1] = "varchar(128)";
            String vs = v.toString();
            vs = vs.length() > 128 ? vs.substring(0, 128) : vs;
            values[2] = "'" + StringEscapeUtils.escapeSql(vs) + "'";
        }
        return values;
    }

    private String getIndexTypes(String indexTable)
    {
        Connection conn = null;
        try
        {
            conn = connectionPool.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getColumns(db, db, indexTable, "i");
            String type = null;
            if (rs.next())
            {
                type = rs.getString("TYPE_NAME");
            }
            rs.close();
            return type;
        }
        catch (Exception e)
        {
            logger.error("", e);
        }
        finally
        {
            close(conn);
        }
        return StringUtils.EMPTY;
    }

    private void close(Connection conn)
    {
        try
        {
            if (null != conn)
            {
                conn.close();
            }
        }
        catch (SQLException e)
        {
            logger.error("", e);
        }
    }

    @Override
    public Entity getSingleEntity(String kind, String key)
    {
        String table = KIND_PREFIX + kind;
        if (!kindTables.contains(table))
        {
            logger.debug("The Kind {} Is Empty!", kind);
            return null;
        }
        String valueTable = table + KIND_VALUE_SUFFIX;
        String sql = "select v.id id, value from " + table + " inner join " + valueTable + " v using(id) where k = '"
                + StringEscapeUtils.escapeSql(key) + "' order by v.o";
        Connection conn = null;
        try
        {
            conn = connectionPool.getConnection();
            ResultSet rs = conn.createStatement().executeQuery(sql);
            logger.debug("GetSingleEntity Sql: {}", sql);
            StringBuilder builder = new StringBuilder();
            int id = 0;
            while (rs.next())
            {
                id = rs.getInt("id");
                builder.append(rs.getString("value"));
            }
            Set<String> indexes = kindIndexColumns.get(kind);
            if (builder.length() > 0)
            {
                Entity entity = parseByteToEntity(kind, key, builder.toString().getBytes(DEFAULT_CHARSET), indexes);
                if (null != entity)
                {
                    entity.setId(id);
                }
                return entity;
            }
        }
        catch (Exception e)
        {
            logger.error("", e);
        }
        finally
        {
            close(conn);
        }
        return null;
    }

    public void setUp()
    {
        Connection conn = null;
        try
        {
            conn = connectionPool.getConnection();
            conn.createStatement().execute("create database ds;");
        }
        catch (Exception e)
        {
            logger.error("", e);
        }
        finally
        {
            close(conn);
        }
    }

    public void tearDown()
    {
        Connection conn = null;
        try
        {
            conn = connectionPool.getConnection();
            conn.createStatement().execute("drop database ds;");
        }
        catch (Exception e)
        {
            logger.error("", e);
        }
        finally
        {
            close(conn);
        }
    }

    public static void main(String[] args)
    {
        // DataStoreServices dataStoreServices = new
        // DataStoreServicesMySqlImpl("ds");
        // Entity entity = new Entity("test", "test_1");
        // entity.setProperty("id", 1);
        // entity.setProperty("value", "test_1", true);
        // entity.setProperty("float", 1.0);
        // entity.setProperty("long", 1l);
        // dataStoreServices.putEntity(entity);
        Object i = 1;
        byte[] b1 = new byte[]
        { 1, 2 };
        System.out.println(i.equals(null));
        System.out.println(b1.equals(new byte[]
        { 1, 2 }));
    }

    @Override
    public void printIndexInformations()
    {
        if (null != indexTables)
        {
            for (String table : indexTables)
            {
                StringBuilder builder = new StringBuilder();
                builder.append("\nIndex Table Name: ");
                builder.append(table);
                builder.append('\n');
                Connection conn = null;
                try
                {
                    conn = connectionPool.getConnection();

                    ResultSet rs = conn.createStatement().executeQuery("select i, value from " + table);
                    while (rs.next())
                    {
                        builder.append('\t');
                        builder.append(rs.getObject("i"));
                        builder.append(": ");
                        builder.append(rs.getInt("value"));
                        builder.append('\n');
                    }
                    logger.info(builder.toString());
                }
                catch (Exception e)
                {
                    logger.error("", e);
                }
                finally
                {
                    close(conn);
                }
            }
        }
    }
}
